﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace DoeObjects
{
	public class Topic
	{
		public int TopicId { get; set; }
		public string TopicName { get; set; }

		public static Topic getByTopicId(int topicId)
		{
			string SQL = "SELECT * FROM [Topic] WHERE TopicId = @TopicId";
			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand(SQL, connection);
			cmd.Parameters.AddWithValue("TopicId", topicId);

			Topic topic = null;
			try
			{
				connection.Open();

				SqlDataReader reader = cmd.ExecuteReader();
				if (reader.Read())
				{
					topic = getCurrentTopic(reader);
				}
				reader.Close();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

			return topic;
		}



		public static List<Topic> getAll()
		{
			List<Topic> topics = new List<Topic>();

			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("SELECT * FROM [Topic]", connection);

			try
			{
				connection.Open();

				SqlDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					topics.Add(getCurrentTopic(reader));
				}
				reader.Close();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			return topics;
		}

		private static Topic getCurrentTopic(SqlDataReader reader)
		{
			Topic topic = null;

			if (reader != null)
			{
				topic = new Topic();
				topic.TopicId = (int)reader["TopicId"];
				topic.TopicName = reader["TopicName"] as string;
			}
			return topic;
		}

		public static List<Topic> getTopicsFromBook(int titleId)
		{
			List<Topic> topics = new List<Topic>();

			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("SELECT * FROM [Topic] INNER JOIN Title_has_topics AS tht ON topic.TopicId = tht.TopicId WHERE tht.TitleId = @id", connection);
			cmd.Parameters.AddWithValue("@id", titleId);

			try
			{
				connection.Open();

				SqlDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					topics.Add(getCurrentTopic(reader));
				}
				reader.Close();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			return topics;
		}

		public static List<Topic> getByName(string topicName, bool useLikeExpression)
		{
			List<Topic> topics = new List<Topic>();

			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("SELECT * FROM [Topic] WHERE TopicName LIKE @topicName", connection);
			SqlParameter topicNameParam = new SqlParameter("@topicName", SqlDbType.VarChar);
			if (useLikeExpression)
				topicNameParam.Value = '%' + topicName + '%';
			else
				topicNameParam.Value = (object)topicName;
			cmd.Parameters.Add(topicNameParam);

			try
			{
				connection.Open();

				SqlDataReader reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					topics.Add(getCurrentTopic(reader));
				}
				reader.Close();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			return topics;
		}

		public static int add(Topic topic)
		{
			int lastInsertedId = 0;
			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("INSERT INTO [Topic] (TopicName) OUTPUT INSERTED.TopicId VALUES (@TopicName)", connection);

			SqlParameter topicName = new SqlParameter("@TopicName", SqlDbType.VarChar);
			topicName.Value = topic.TopicName;
			cmd.Parameters.Add(topicName);

			try
			{
				connection.Open();
				Object res = cmd.ExecuteScalar();
				if (res != null)
				{
					lastInsertedId = (int)res;
				}
			}
			catch (SqlException er)
			{
				throw er;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

			return lastInsertedId;
		}

		private static bool update(Topic topic)
		{
			int affectedRows = 0;
			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("UPDATE [Topic] SET TopicName = @TopicName WHERE TopicId = @id", connection);
			cmd.Parameters.AddWithValue("@id", topic.TopicId);
			SqlParameter topicName = new SqlParameter("@TopicName", SqlDbType.VarChar);
			topicName.Value = topic.TopicName;
			cmd.Parameters.Add(topicName);

			try
			{
				connection.Open();
				affectedRows = cmd.ExecuteNonQuery();
			}
			catch (SqlException er)
			{
				throw er;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

			return (affectedRows > 0);
		}

		public bool save()
		{
			if (this.TopicId == 0)
			{
				this.TopicId = Topic.add(this);
				return (this.TopicId != 0);
			}
			else
			{
				return Topic.update(this);
			}
		}
	}
}
